Limitations of a dataset

SQL limitations of a dataset in terms of size

• This is determined by the number of fields added to the dataset, and is a complex interaction between the number of each of the different types of data.

• 1024 is the absolute maximum of integer fields allowed.

• Maximum column count: 1024 columns per table.

• Maximum row size, not the count: 8060.

• • To determine if a row size is valid or not: use the following calculation:

  • • MaxRowSize = 8060;

    • fixedDataSize = 4 + (integerColumnCount * 4) + (doubleColumnCount * 8) + (timestampColumnCount * 8);

    • variableDataSize = (2 + (stringColumnCount * 24));

    • nullBitmapSize = (2 + ((columns.Length + 7) / 8));

    • overhead = 4;

    • (fixedDataSize + variableDataSize + nullBitmapSize + overhead) must be less than MaxRowSize)

SQL Command timeout

NOTE: SQL Command timeouts are set to a default of 30 seconds, if the QueryTimeout is not specified. This timeout applies to all data preparation operations.

To define the timeout for all operations, edit the registry by:

1. Click the Windows [Start] button, and in the search box, type in regedit.

2. Open the Registry Editor by double clicking [Regedit.exe].

3. Find either of the following folders:

   • for Windows (32-bit): HKEY_LOCAL_MACHINE\SOFTWARE\Proficy CSense

   • for Windows (64-bit): HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Proficy CSense

4. Either change the QueryTimeout value in the folder, or add a QueryTimeout value.

5. To add a QueryTimeout value, you need to add a DWORD value.

   • To do this, right click on the folder [Proficy CSense] > New > DWORD Value.

   • In the textbox, type in QueryTimeout

6. Double click the QueryTimeout to edit the DWORD value.

   • Select Decimal as the Base type.

   • Define the timeout for SQL queries in seconds.

Valid field names

• 126 characters maximum

• no spaces between words

• only alphanumeric and numbers allowed

• first character can't be a number

• underscores are allowed

Valid dataset name

• any name is allowed

Double values - minimum and maximum values

• the value can only store the first fifteen significant digits

• Smallest DOUBLE value: -1.79x10308

• Largest DOUBLE value: -1.79x10308

Integer values - minimum and maximum values

• Smallest INTEGER value: -2 147 483 648

• Largest INTEGER value: +2 147 483 647

Timestamp values - minimum and maximum values

• The date can reach from the year 1 to the year 9999

• Minimum value: 0001/01/01 00:00:00.000

• Maximum value: 9999/12/31 23:59:59.999

String values - maximum value

• 1,073,741,824 characters